import pymysql
from pyecharts.charts import Bar
from pyecharts import options as opts


def get_top_15_data(sql_query):
    """
    通用函数，用于从数据库获取前15的数据
    """
    # 数据库连接配置
    db_config = {
        'host': 'localhost',
        'port': 3306,
        'user': 'root',
        'password': '123456',
        'database': 'test',
        'charset': 'utf8mb4'
    }
    # 连接数据库
    conn = pymysql.connect(**db_config)
    try:
        with conn.cursor() as cursor:
            cursor.execute(sql_query)
            results = cursor.fetchall()
            user_ids = [result[0] for result in results]
            counts = [result[1] for result in results]
            return user_ids, counts
    finally:
        conn.close()


def generate_chart(title, user_ids, counts):
    """
    生成柱状图
    """
    bar = (
        Bar()
        .add_xaxis(user_ids)
        .add_yaxis(title, counts)
        .set_global_opts(
            title_opts=opts.TitleOpts(title=title),
            xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 45}),
            toolbox_opts=opts.ToolboxOpts(is_show=True)
        )
    )
    return bar.render_embed()


if __name__ == "__main__":
    # 获取点赞数前15的数据
    voteup_sql = "SELECT UserID, VoteupCount FROM answers_1 ORDER BY VoteupCount DESC LIMIT 15"
    voteup_user_ids, voteup_counts = get_top_15_data(voteup_sql)
    voteup_chart = generate_chart("点赞数前十五的回答", voteup_user_ids, voteup_counts)

    # 获取评论数前15的数据
    comment_sql = "SELECT UserID, CommentCount FROM answers_1 ORDER BY CommentCount DESC LIMIT 15"
    comment_user_ids, comment_counts = get_top_15_data(comment_sql)
    comment_chart = generate_chart("评论数前十五的回答", comment_user_ids, comment_counts)

    # 打印生成的图表HTML
    print("点赞数前15图表：")
    print(voteup_chart)
    print("\n评论数前15图表：")
    print(comment_chart)